﻿/*
Commerce by Onex – eCommerce software platform (www.ecommerce.byonex.com)
Copyright (C) 2008-2010 by Onex Group

This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
 
 */
using System;
using System.Collections.Generic;
using System.Text;
using ShopModel;
using System.Data;
using System.Data.SqlClient;


namespace ShopDAO
{
    public class NewsletterDAO
    {

        private string connectionString;

        private SqlConnection Connection()
        {
            return new SqlConnection(connectionString);
        }

        public NewsletterDAO()
        {
            connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["csShop"].ConnectionString;
        }

        /// <summary>
        /// Pobiera subskrypcję w oparciu o jej id(Downloads newsletter on the basis of its id.)
        /// </summary>
        /// <param name="IdNewsLetter">Id biuletynu do pobrania(NewsLetter id to download.)</param>
        /// <param name="LanguageID">Id języka w jakim wyświetlana jest strona(Language id in which website is displayed.)</param>
        /// <returns>Object NewsLetter przechowujący treść meila do wysłania (NewsLetter object containing the message meila.)</returns>
        public Newsletter GetNewsletter(int NewsletterID, int? LanguageID)
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = sqlC.CreateCommand();
            SqlDataReader sqlR;
            Newsletter result = null;

            command.CommandText = "GetNewsletter";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@IdNewsletter", NewsletterID));
            command.Parameters.Add(new SqlParameter("@LanguageID", LanguageID));

            try
            {
                sqlC.Open();
                sqlR = command.ExecuteReader();

                if (sqlR.Read())
                {
                    result = new Newsletter((int)sqlR["IdNewsletter"], (string)sqlR["Title"], (string)sqlR["Description"], (string)sqlR["Content"], (DateTime)sqlR["NewsDate"], (DateTime)sqlR["NewsDateSend"], (bool)sqlR["IsSent"]);
                }

                sqlR.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result;
        }

        /// <summary>
        /// Zapisuje treść biuletynu, jeśli istnieje uaktualnia(Saves new newsletter, updates content if it exists.)
        /// </summary>
        /// <param name="NewsToSave">Objekt typu NewsLettre przechowujący treśc biuletynu (NewsLetter object containing mail that is to be saved.)</param>
        /// <param name="LanguageID">Id języka w jakim wyświetlana jest strona (Language id in which website is displayed.)</param>
        /// <returns>Id dodanego biuletynu (Id of the added newsletter.)</returns>
        public int SaveNewsletter(Newsletter NewsletterToSave, int? LanguageID)
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = sqlC.CreateCommand();
            SqlDataReader sqlR;

            int result = -1;

            command.CommandText = "SaveNewsletter";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@IDnewsLetter", NewsletterToSave.NewsID));
            command.Parameters.Add(new SqlParameter("@Title", NewsletterToSave.Title));
            command.Parameters.Add(new SqlParameter("@Description", NewsletterToSave.Description));
            command.Parameters.Add(new SqlParameter("@Content", NewsletterToSave.Content));
            command.Parameters.Add(new SqlParameter("@NewsDate", NewsletterToSave.NewsDate));
            command.Parameters.Add(new SqlParameter("@NewsDateSend", NewsletterToSave.NewsDateSend));
            command.Parameters.Add(new SqlParameter("@LanguageID", LanguageID));
            command.Parameters.Add(new SqlParameter("@IsSent", NewsletterToSave.IsSent));

            try
            {
                sqlC.Open();
                sqlR = command.ExecuteReader();

                if (sqlR.Read())
                    result = (int)sqlR["NewsID"];

                sqlR.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result;
        }

        /// <summary>
        /// Usuwa biuletyn z tabeli (Deletes newsletter from table.)
        /// </summary>
        /// <param name="IdNewsLetter">Id subskrypcji do usunięcia (ID of newsletter which is going to be removed)</param>
        /// <returns>Zwraca 'true' jeśli operacja usuwania zakończona powodzeniem(If ‘true’, process of removal succeeded.)</returns>
        public bool DeleteNewsletter(int NewsletterID)
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = sqlC.CreateCommand();
            bool result = false;

            command.CommandText = "DeleteNewsletter";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@IdNewsletter", NewsletterID));

            try
            {
                sqlC.Open();
                int rows = command.ExecuteNonQuery();

                if (rows > 0)
                    result = true;
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result;
        }

        /// <summary>
        /// Usuwa mail urzytkownika z tabeli maili oczekujących na wysłanie (Deletes user’s mail form the to be send mails.)
        /// </summary>
        /// <param name="IdNewsLetter">Id biuletynu (News id.)</param>
        /// <param name="Email">Meil użytkownika który otrzymał już wiadomość (User’s that has already received the news email.)</param>
        /// <returns>Zwraca 'true' jeśli operacja usuwania zakończona powodzeniem (If ‘true’, process of removal succeeded.)</returns>
        public bool DeleteNewsletterEmail(int NewsletterID, string Email)
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = sqlC.CreateCommand();
            bool result = false;

            command.CommandText = "DeleteNewsletterEmail";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@NewsletterID", NewsletterID));
            command.Parameters.Add(new SqlParameter("@Email", Email));

            try
            {
                sqlC.Open();
                int rows = command.ExecuteNonQuery();

                if (rows > 0)
                    result = true;
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result;
        }

        /// <summary>
        /// Pobiera wszystkie subskrypcje z tabeli NewsLetter (Downloads all the news from the NewsLetter table.) 
        /// </summary>
        /// <param name="LanguageID">Id języka w jakim syświetlana jest strona (Language id in which website is displayed.)</param>
        /// <returns>Lista objektów typu NewsLetter, zawierająca trść subskrypcji pobranych z tabeli newsletter (List of NewsLetter object, downloaded list of mails from the newsletter table.)</returns>
        public List<Newsletter> GetNewsletters(int? LanguageID)
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = sqlC.CreateCommand();
            SqlDataReader sqlR;
            List<Newsletter> result = new List<Newsletter>();

            command.CommandText = "GetNewsletter";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@LanguageID", LanguageID));

            try
            {
                sqlC.Open();
                sqlR = command.ExecuteReader();

                while (sqlR.Read())
                {
                    result.Add(new Newsletter((int)sqlR["IdNewsletter"], (string)sqlR["Title"], (string)sqlR["Description"], (string)sqlR["Content"], (DateTime)sqlR["NewsDate"], (DateTime)sqlR["NewsDateSend"], (bool)sqlR["IsSent"]));
                }

                sqlR.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result;
        }

        /// <summary>
        /// Pobiera id wiadomości kótrych data wysłania juz mineła/nastała (Downloads id of messages that send date will expire.)
        /// </summary>
        /// <returns>Lista przechowująca id subskrypcji, które należy wysłać (id list of messages to be sent.)</returns>
        public Dictionary<int, Newsletter> GetNewsletterToSend()
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = sqlC.CreateCommand();
            SqlDataReader sqlR;
            Dictionary<int, Newsletter> result = new Dictionary<int, Newsletter>();

            command.CommandText = "GetNewsletter";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@ToSend", true));

            try
            {
                sqlC.Open();
                sqlR = command.ExecuteReader();

                while (sqlR.Read())
                {
                    result.Add((int)sqlR["IdLanguage"], new Newsletter((int)sqlR["IdNewsletter"], (string)sqlR["Title"], (string)sqlR["Description"], (string)sqlR["Content"], (DateTime)sqlR["NewsDate"], (DateTime)sqlR["NewsDateSend"], (bool)sqlR["IsSent"]));
                }

                sqlR.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result;
        }

        /// <summary>
        /// Pobiera maile na które należy wysłać wiadomośc z tabeli NewsLetterEmail (Downloads user's mails from the to be sent mails from the NewsLetterEmail table.)
        /// </summary>
        /// <param name="idNews">Id biuletynu do wysłania (Id of messages to be sent.)</param>
        /// <returns>Lista objektów typu NewLetterEmail, zawierająca maile i id subskrypacji do wysłania (List of NewLetterEmail objects, mail list of which that are to receive the message.)</returns>
        public List<NewsletterEmail> GetNewsletterMailingList(int NewsletterID, int Count)
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = sqlC.CreateCommand();
            SqlDataReader sqlR;
            List<NewsletterEmail> result = new List<NewsletterEmail>();

            command.CommandText = "GetNewsletterMailingList";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@NewsletterID", NewsletterID));
            command.Parameters.Add(new SqlParameter("@Count", Count));

            try
            {
                sqlC.Open();
                sqlR = command.ExecuteReader();

                while (sqlR.Read())
                    result.Add(new NewsletterEmail((int)sqlR["IdNews"], (string)sqlR["Email"], (int)sqlR["PrefferedlanguageID"]));

                sqlR.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result;
        }

    }
}
